package idv.arthur.work;

import java.io.IOException;
import java.io.StringReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.google.gson.stream.JsonReader;

public class QueryPath extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    public QueryPath() {
        super();
    }
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    	doPost(request,response);
    }

	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		
		Widgets wid = new Widgets();
		request.setCharacterEncoding( wid.UIcodeSet );
		response.setContentType("application/text");
		response.setCharacterEncoding( wid.UIcodeSet );
		
		boolean isMatch = false;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		ResultSet rs2 = null;
		String dePath = "";
		String errPars = "}";
		
		int squadCount = Integer.parseInt(request.getParameter("squadCount"));
		String strSQL = "SELECT t1.deplmainSN,t1.depldetailid,t2.depPath FROM mpdeplunitsqu t1,depldetail t2 WHERE t2.localFile=? AND t1.deplmainSN=t2.deplmainSN AND t1.depldetailid=t2.depldetailid GROUP BY t1.deplmainSN,t1.depldetailid,t2.depPath HAVING COUNT(t1.deplmainSN)=? ORDER BY t1.deplmainSN DESC,t1.depldetailid DESC";
		
		JsonReader reader = new JsonReader(new StringReader( request.getParameter("sqCheck") ));		//sqCheck為所提交的內容
		reader.beginArray();
		ArrayList<InfoStruc> sqs = new ArrayList<InfoStruc>();
		while (reader.hasNext()) {
			InfoStruc sq = new InfoStruc();
			reader.beginObject();
			reader.nextName();
			sq.setId(reader.nextString());
			reader.nextName();		//資訊群組ID
			sq.setName(reader.nextString());
			reader.endObject();
			sqs.add(sq);
		}
		reader.endArray();		
		
		try {
			conn = wid.getConn(true);
			errPars = wid.toJ("localFile", request.getParameter("fn"))+wid.toJ("squadCount", squadCount);
			ps = conn.prepareStatement(strSQL);
			ps.setString(1, request.getParameter("fn"));
			ps.setInt(2, squadCount);
			rs = ps.executeQuery();
			
			strSQL = "SELECT t1.bssid,t1.subsysid,t1.squadid FROM mpdeplunitsqu t1,depldetail t2 WHERE t1.deplmainSN=t2.deplmainSN AND t1.depldetailid=t2.depldetailid AND t1.deplmainSN=? AND t1.depldetailid=?";
			while( rs.next() ) {
				errPars = wid.toJ("deplmainSN", rs.getInt("deplmainSN") )+ wid.toJend("depldetailid", rs.getInt("depldetailid") );
				ps = conn.prepareStatement(strSQL);
				ps.setInt(1, rs.getInt("deplmainSN"));
				ps.setInt(2, rs.getInt("depldetailid"));
				rs2 = ps.executeQuery();
				while ( rs2.next() ) {
					isMatch = false;
					for (int i=0;i<sqs.size();i++) {
						if ( rs2.getString("subsysid").equalsIgnoreCase(sqs.get(i).getId()) && rs2.getString("squadid").equalsIgnoreCase(sqs.get(i).getName()) ) {
							isMatch = true;
							dePath = rs.getString("depPath");
							break;
						}
					}
					if ( isMatch ) { break; }
				}
				
				if ( isMatch ) { break; }
			}
		} catch ( SQLException e) {
			wid.kem(conn, ps, e, wid.toEType("SQLException")+errPars, wid.isPrint2Console );
		} catch ( Exception e) {
			wid.kem(conn, ps, e, wid.toEType("Exception")+errPars, wid.isPrint2Console );
		} finally {
			try {
				if (reader != null) { reader.close(); }
				if (rs != null) {rs.close();}
				if (rs2 != null) {rs2.close();}
				if (ps!= null) {ps.close();}
				if (conn!= null) {conn.close();}
			} catch (SQLException e) {
				System.out.println(wid.errProcess(e));
			}
		}
		
		response.getWriter().write(dePath);
	}
}